package com.portalEsporteClubeBahia.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.portalEsporteClubeBahia.app.ApplicationException;
import com.portalEsporteClubeBahia.app.vo.ChampionshipVO;
import com.portalEsporteClubeBahia.app.vo.ClassificationVO;
import com.portalEsporteClubeBahia.app.vo.TeamVO;

public class ChampionshipDAO extends DAOFactory {

	public ChampionshipDAO() throws ApplicationException {
		super();
	}
	
	public List<ChampionshipVO> findAll() throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ChampionshipVO> list = new ArrayList<ChampionshipVO>();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME, YEARS FROM TBL_CHAMPIONSHIP ORDER BY YEARS DESC, NAME");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			
			while(rs.next()){
				ChampionshipVO champ = new ChampionshipVO();
				champ.setId(rs.getInt(1));
				champ.setName(rs.getString(2));
				champ.setYear(rs.getInt(3));
				list.add(champ);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
	}
	
	public List<ClassificationVO> findByFilter(ClassificationVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ClassificationVO> list = new ArrayList<ClassificationVO>();
		
		StringBuffer sql = new StringBuffer("SELECT * FROM TBL_CLASSIFICATION CL, TBL_TEAM T, TBL_CHAMPIONSHIP CH ");
		sql.append(" WHERE ");
		sql.append(" T.ID = CL.TEAMID ");
		sql.append(" AND CH.ID = CL.CHAMPIONSHIPID ");
		sql.append(" AND (? = 0 OR CL.TEAMID = ?) ");
		sql.append(" AND (? = 0 OR CL.CHAMPIONSHIPID = ?) ");
		sql.append(" ORDER BY CL.POSITION ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			int cont = 1;
			if(filter.getTeam().getId()==null){
				ps.setInt(cont++, 0);
				ps.setInt(cont++, 0);
			}else{
				ps.setInt(cont++, filter.getTeam().getId());
				ps.setInt(cont++, filter.getTeam().getId());
			}
			if(filter.getChampionship().getId()==null){
				ps.setInt(cont++, 0);
				ps.setInt(cont++, 0);
			}else{
				ps.setInt(cont++, filter.getChampionship().getId());
				ps.setInt(cont++, filter.getChampionship().getId());
			}
			rs = ps.executeQuery();
			
			while(rs.next()){
				ClassificationVO vo = new ClassificationVO();
				vo.setId(rs.getInt("CL.ID"));
				vo.setPoints(rs.getShort("CL.POINTS"));
				vo.setPosition(rs.getShort("CL.POSITION"));
				vo.setWins(rs.getShort("CL.WINS"));
				vo.setDraws(rs.getShort("CL.DRAWS"));
				vo.setLosses(rs.getShort("CL.LOSSES"));
				vo.setTeam(new TeamVO());
				vo.getTeam().setId(rs.getInt("T.ID"));
				vo.getTeam().setName(rs.getString("T.NAME"));
				vo.getChampionship().setId(rs.getInt("CL.CHAMPIONSHIPID"));
				vo.getChampionship().setName(rs.getString("CH.NAME"));
				vo.getChampionship().setYear(rs.getInt("CH.YEARS"));
				list.add(vo);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public List<ChampionshipVO> findByFilter(ChampionshipVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ChampionshipVO> list = new ArrayList<ChampionshipVO>();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME, YEARS FROM TBL_CHAMPIONSHIP ");
		sql.append(" WHERE (? = 0 OR YEARS = ?) ");
		if(filter.getName()!=null && !filter.getName().equalsIgnoreCase(""))
			sql.append(" AND NAME LIKE '%"+filter.getName()+"%' ");
		sql.append(" ORDER BY YEARS DESC, NAME ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			if(filter.getYear()==null || filter.getYear()==0){
				ps.setInt(1, 0);
				ps.setInt(2, 0);
			}else{
				ps.setInt(1, filter.getYear());
				ps.setInt(2, filter.getYear());
			}
			rs = ps.executeQuery();
			
			while(rs.next()){
				ChampionshipVO championship = new ChampionshipVO();
				championship.setId(rs.getInt("ID"));
				championship.setName(rs.getString("NAME"));
				championship.setYear(rs.getInt("YEARS"));
				list.add(championship);
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return list;
		
	}
	
	public void insert(ChampionshipVO championship) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("INSERT INTO TBL_CHAMPIONSHIP (NAME,YEARS) VALUES (?,?) ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, championship.getName());
			ps.setInt(2, championship.getYear());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void update(ChampionshipVO championship) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("UPDATE TBL_CHAMPIONSHIP SET NAME=?,YEARS=? WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, championship.getName());
			ps.setInt(2, championship.getYear());
			ps.setInt(3, championship.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}
	
	public void delete(ChampionshipVO championship) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		StringBuffer sql = new StringBuffer("DELETE FROM TBL_CHAMPIONSHIP WHERE ID =? ");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, championship.getId());
			ps.executeUpdate();
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, null);
		}
		
	}

	public ChampionshipVO findByPrimaryKey(ChampionshipVO filter) throws ApplicationException{
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		ChampionshipVO championship = new ChampionshipVO();
		
		StringBuffer sql = new StringBuffer("SELECT ID, NAME,YEARS FROM TBL_CHAMPIONSHIP WHERE ID = ?");
		
		try{
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setInt(1, filter.getId());
			rs = ps.executeQuery();
			
			while(rs.next()){
				championship.setId(rs.getInt("ID"));
				championship.setName(rs.getString("NAME"));
				championship.setYear(rs.getInt("YEARS"));
			}
			
		}catch(Exception e){
			throw new ApplicationException(e);
		}finally{
			this.close(conn, ps, rs);
		}
		
		return championship;
		
	}

}
